package work.mediway.mdm.core.helper;

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Optional;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import work.mediway.mdm.core.constant.MdmConstant;
import work.mediway.mdm.core.enums.TablePrefixEnum;
import work.mediway.mdm.core.exception.DataException;
import work.mediway.mdm.core.util.SqlSessionUtil;
import work.mediway.mdm.core.vo.TableVO;

/**
 * @author zhongyj <1126834403@qq.com><br/>
 * @date 2021/3/5
 */
@Slf4j
@Component
@RequiredArgsConstructor
public class BaseDatabaseHelper {

    private final JdbcTemplate jdbcTemplate;
    private final SqlSessionTemplate sqlSessionTemplate;
    /*private final IDatabaseConnectionService databaseConnectionService;*/

    /**
     * 生成表的Code字段
     *
     * @param tablePrefixEnum TablePrefixEnum
     * @return 下一条插入数据的Code
     */
    public String buildCode(TablePrefixEnum tablePrefixEnum) {
        String sql = "SELECT SUBSTRING(MAX(" + tablePrefixEnum.getColumn() + "), " + (tablePrefixEnum.getPrefix().length() + 1) + ") + 1 from mdm_User." + tablePrefixEnum.getTable();
        List<String> idByDatabase = StrUtil.split(jdbcTemplate.queryForObject(sql, String.class), ".");
        String nextPrimaryKey = (CollUtil.isEmpty(idByDatabase) || StrUtil.equalsIgnoreCase("null", idByDatabase.get(0))) ? "1" : idByDatabase.get(0);
        return tablePrefixEnum.getPrefix() + StrUtil.padPre(nextPrimaryKey, MdmConstant.PRIMARY_KEY_NUM_LEN, "0");
    }

    /**
     * 生成自增
     *
     * @param tablePrefixEnum TablePrefixEnum
     * @return Long
     */
    public Long buildIncrease(TablePrefixEnum tablePrefixEnum) {
        String sql = "SELECT MAX(" + tablePrefixEnum.getColumn() + ")" + " + 1 from mdm_User." + tablePrefixEnum.getTable();
        Long next = Optional.ofNullable(jdbcTemplate.queryForObject(sql, Long.class)).orElse(1L);
        return ObjectUtil.isEmpty(next) ? 1L : next;
    }

    public Long buildIncrease(String column, String tableCode) {
        String sql = "SELECT MAX(" + column + ")" + " + 1 from mdm_User." + tableCode;
        Long next = Optional.ofNullable(jdbcTemplate.queryForObject(sql, Long.class)).orElse(1L);
        return ObjectUtil.isEmpty(next) ? 1L : next;
    }

    /**
     * 获取schema下的所有表
     *
     * @param sqlSessionTemplate 直接注入
     * @param schema             Schema
     * @param tableCode          可以为null
     * @return 返回表信息
     */
    public List<TableVO> getTable(SqlSessionTemplate sqlSessionTemplate, String schema, String tableCode) {
        if (ObjectUtil.isEmpty(sqlSessionTemplate)) {
            sqlSessionTemplate = this.sqlSessionTemplate;
        }
        Connection connection = SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
        return this.getTable(connection, schema, tableCode);
    }

    /**
     * 获取当前系统数据库连接
     *
     * @return Connection
     */
    public Connection getLocalConnection() {
        return SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
    }

    public List<TableVO> getTable(String tableCode) {
        Connection connection = SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
        return this.getTable(connection, null, tableCode);
    }

    /**
     * 获取schema下的所有表
     *
     * @param schema    String
     * @param tableCode String
     * @return List<TableVO>
     */
    public List<TableVO> getTable(String schema, String tableCode) {
        Connection connection = SqlSessionUtil.getSqlSession(sqlSessionTemplate).getConnection();
        return this.getTable(connection, schema, tableCode);
    }

    /**
     * 获取schema下的所有表
     *
     * @param connection Connection
     * @param schema     String
     * @param tableCode  String
     * @return List<TableVO>
     */
    public List<TableVO> getTable(Connection connection, String schema, String tableCode) {
        if (StrUtil.isBlank(tableCode)) {
            tableCode = null;
        }
        List<TableVO> table = CollUtil.newArrayList();
        DatabaseMetaData databaseMetaData;
        try {
            databaseMetaData = connection.getMetaData();
            ResultSet rs = databaseMetaData.getTables(connection.getCatalog(), schema, tableCode, null);
            while (rs.next()) {
                if (StrUtil.isNotBlank(rs.getString("TABLE_NAME"))) {
                    TableVO tableVO = new TableVO();
                    tableVO.setTableCode(rs.getString("TABLE_NAME"));
                    String remarks = rs.getString("REMARKS");
                    tableVO.setTableName(StrUtil.isBlank(remarks) ? tableVO.getTableCode() : remarks);
                    tableVO.setSchemaName(schema);
                    table.add(tableVO);
                } else {
                    throw new DataException(StrUtil.format("表 [{}] 不存在", tableCode));
                }
            }
        } catch (SQLException e) {
            throw new DataException(e.getMessage());
        }
        return table;
    }

    /**
     * 获取表schema
     *
     * @param connection Connection
     * @return List<String>
     */
    public List<String> getSchema(Connection connection) {
        List<String> schema = CollUtil.newArrayList();
        try {
            ResultSet catalogs = connection.getMetaData().getCatalogs();
            while (catalogs.next()) {
                schema.add(catalogs.getString("TABLE_CAT"));
            }
        } catch (SQLException e) {
            log.error(e.getMessage());
            return null;
        }
        return schema;
    }



    /*public DatabaseConnectionDO getDatabaseConnectionDoByDatabaseId(Long databaseId) {
        DatabaseConnectionDO databaseConnectionDO = SpringUtil.getBean(DatabaseConnectionImpl.class).getDatabaseConnectionDO(databaseId);
        if (ObjectUtil.isEmpty(databaseConnectionDO)) {
            throw new DataException("请确认是否添加数据源配置信息");
        }
        return databaseConnectionDO;
    }

    public Connection getConnectionByDatabaseId(Long databaseId) {
        DatabaseConnectionDO databaseConnectionDO = getDatabaseConnectionDoByDatabaseId(databaseId);
        DatabaseConnectionImpl databaseConnection = new DatabaseConnectionImpl();
        DatabaseMeta databaseMeta = databaseConnection.getDatabaseMeta(databaseConnectionDO);
        Database db = new Database(null, databaseMeta);
        try {
            db.connect();
        } catch (KettleDatabaseException e) {
            throw new DataException("获取数据库连接失败");
        }
        return db.getConnection();
    }*/
}
